#include "coinmodel.h"

#include <QDebug>
#include <QSqlQuery>
#include <QSqlRecord>
#include <QDir>
#include <QFileInfo>
#include <QTemporaryFile>
#include <QUrl>
#include <QDateTime>
#include <QDesktopServices>
#include <QImage>

const char* CoinModel::DEFAULT_FILTER = "status='demo'";

const char* CoinModel::COLUMN_NAMES[] = {
    "id",
    "title",
    "value",
    "unit",
    "country",
    "year",
    "period",
    "mint",
    "mintmark",
    "issuedate",
    "type",
    "series",
    "subjectshort",
    "status",
    "material",
    "fineness",
    "grade",
    "mintage",
    "price1",
    "price2",
    "price3",
    "price4",
    "subject",
    "image",
    "features",
    "createdat",
    "updatedat",
    NULL
};

CoinModel::CoinModel(QObject *parent, QSqlDatabase db) :
    QSqlTableModel(parent, db)
{
    int idx = 0;
    QHash<int, QByteArray> roleNames;
    while(COLUMN_NAMES[idx]) {
        roleNames[Qt::UserRole + idx + 1] = COLUMN_NAMES[idx];
        idx++;
    }
    roleNames[Qt::UserRole + idx + 1] = "count";
    m_countRoleIndex = Qt::UserRole + idx + 1;
    roleNames[Qt::UserRole + idx + 2] = "imgheight";
    m_imgheightRoleIndex = Qt::UserRole + idx + 2;
    roleNames[Qt::UserRole + idx + 3] = "imgwidth";
    m_imgwidthRoleIndex = Qt::UserRole + idx + 3;
    roleNames[Qt::UserRole + idx + 4] = "obverseimg";
    m_obverseimgRoleIndex = Qt::UserRole + idx + 4;
    roleNames[Qt::UserRole + idx + 5] = "reverseimg";
    m_reverseimgRoleIndex = Qt::UserRole + idx + 5;
    setRoleNames(roleNames);
}

// Work around QTBUG-15036
bool CoinModel::select()
{
    bool res;

    res = QSqlTableModel::select();
    while (canFetchMore())
        fetchMore();

    return res;
}

bool CoinModel::open(const QString & file_name)
{
    QUrl url(file_name);

    QString path(url.toLocalFile());
    QFileInfo file = QFileInfo(path);
    if (!file.exists())
        return false;
    if (database().isOpen())
        database().close();
    database().setDatabaseName(QDir::toNativeSeparators(file.absoluteFilePath()));
    if (database().open()) {
        if (!checkVersion()) {
            if (!updateVersion()) {
                return false;
            }
        }

        QString sql;
        QSqlQuery query;

        m_settings.setValue("lastCollection", file_name);

        setTable("coins");
        setFilter(DEFAULT_FILTER);
        setSort(fieldIndex("year"), Qt::AscendingOrder);
        select();

        m_view = CommemorativeWorld;
        sql = "SELECT value FROM settings WHERE title='mobile_view'";
        query = QSqlQuery(sql, database());
        if (query.first()) {
            QVariant value = query.record().value(0);
            if (value.isValid())
                if (value.toString() == "CommemorativeCountry")
                    m_view = CommemorativeCountry;
        }
        emit changed();

        m_seriesModel.clear();
        if (m_view == CommemorativeWorld)
            m_seriesModel.append(tr("All countries"));
        else
            m_seriesModel.append(tr("All series"));

        if (m_view == CommemorativeWorld)
            sql = "SELECT DISTINCT country FROM coins";
        else
            sql = "SELECT DISTINCT series FROM coins";
        query = QSqlQuery(sql, database());
        while (query.next()) {
            QVariant value = query.record().value(0);
            if (value.isValid()) {
                QString series = value.toString();
                if (!series.isEmpty()) {
                    m_seriesModel.append(series);
                }
            }
        }
        m_seriesId = 0;
        // TODO: Add item for blank series

        return true;
    }

    return false;
}

bool CoinModel::update(const QString & file_name)
{
    bool result = false;
    QUrl url(file_name);

    QString path(url.toLocalFile());
    QFileInfo file = QFileInfo(QDir::toNativeSeparators(path));
    if (file.exists())
    {
        QSqlDatabase updDb = QSqlDatabase::addDatabase("QSQLITE", "upd");
        updDb.setDatabaseName(file.absoluteFilePath());
        if (updDb.open()) {
            QSqlTableModel* model = new QSqlTableModel(this, updDb);
            model->setTable("coins");
            model->select();

            QSqlRecord record;
            QSqlQuery query, queryDst;
            for (int i = 0; i < model->rowCount(); i++) {
                record = model->record(i);
                record.setNull("id");

                query = QSqlQuery(updDb);
                query.prepare("SELECT image FROM images WHERE id=?");
                query.addBindValue(record.value("obverseimg"));
                query.exec();
                if (query.first()) {
                    QVariant value = query.record().value(0);

                    queryDst = QSqlQuery(database());
                    queryDst.prepare("INSERT INTO images (image) VALUES (?)");
                    queryDst.addBindValue(value);
                    queryDst.exec();

                    value = queryDst.lastInsertId();
                    record.setValue("obverseimg", value);
                }
                else {
                    record.setNull("obverseimg");
                }
                query = QSqlQuery(updDb);
                query.prepare("SELECT image FROM images WHERE id=?");
                query.addBindValue(record.value("reverseimg"));
                query.exec();
                if (query.first()) {
                    QVariant value = query.record().value(0);

                    queryDst = QSqlQuery(database());
                    queryDst.prepare("INSERT INTO images (image) VALUES (?)");
                    queryDst.addBindValue(value);
                    queryDst.exec();

                    value = queryDst.lastInsertId();
                    record.setValue("reverseimg", value);
                }
                else {
                    record.setNull("reverseimg");
                }


                insertRecord(-1, record);
            }

            QDate currentDate = QDate::currentDate();
            query = QSqlQuery(updDb);
            query.prepare("SELECT version FROM updates");
            query.exec();
            while (query.next()) {
                queryDst = QSqlQuery(database());
                queryDst.prepare("INSERT INTO updates (version, date) VALUES (?, ?)");
                queryDst.addBindValue(query.value(0));
                queryDst.addBindValue(currentDate.toString(Qt::ISODate));
                queryDst.exec();
            }

            submitAll();

            delete model;
            updDb.close();

            result = true;
        }
    }

    QSqlDatabase::removeDatabase("upd");

    return result;
}

bool CoinModel::isApplied(const QString & date)
{
    QSqlQuery query = QSqlQuery(database());
    query.prepare("SELECT COUNT(*) FROM updates WHERE version=?");
    query.addBindValue(date);
    query.exec();
    query.first();
    return query.value(0).toInt() > 0;
}

bool CoinModel::isEmtyField(const QModelIndex &index, const QString field) const
{
    QModelIndex modelIndex;
    QVariant data;
    modelIndex = this->index(index.row(), fieldIndex(field));
    data = modelIndex.data();
    if (data.isNull() || (data.canConvert(QVariant::String) && data.toString().isEmpty()))
        return true;
    else
        return false;
}

bool CoinModel::checkVersion()
{
    QSqlQuery query = QSqlQuery(database());
    query.prepare("SELECT value FROM settings WHERE title=?");
    query.addBindValue("Version");
    query.exec();
    if (query.first()) {
        QString version = query.value(0).toString();
        return (version == "M2");
    }

    return false;
}

bool CoinModel::updateVersion()
{
    QString sql;

    sql = "ALTER TABLE coins ADD COLUMN material TEXT";
    QSqlQuery(sql, database());
    sql = "ALTER TABLE coins ADD COLUMN fineness INTEGER";
    QSqlQuery(sql, database());
    sql = "ALTER TABLE coins ADD COLUMN diameter NUMBER";
    QSqlQuery(sql, database());
    sql = "ALTER TABLE coins ADD COLUMN thickness NUMBER";
    QSqlQuery(sql, database());
    sql = "ALTER TABLE coins ADD COLUMN weight NUMBER";
    QSqlQuery(sql, database());
    sql = "ALTER TABLE coins ADD COLUMN quality TEXT";
    QSqlQuery(sql, database());
    sql = "ALTER TABLE coins ADD COLUMN dateemis TEXT";
    QSqlQuery(sql, database());
    sql = "ALTER TABLE coins ADD COLUMN catalognum1 TEXT";
    QSqlQuery(sql, database());
    sql = "ALTER TABLE coins ADD COLUMN rarity TEXT";
    QSqlQuery(sql, database());
    sql = "ALTER TABLE coins ADD COLUMN quantity INTEGER";
    QSqlQuery(sql, database());

    sql = "CREATE TABLE images (\
            id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\
            image BLOB\
        )";
    QSqlQuery(sql, database());

    sql = "CREATE TABLE updates (\
            id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\
            version TEXT,\
            date TEXT\
        )";
    QSqlQuery(sql, database());
/*
    setTable("coins");
    select();

    QSqlQuery query;
    QVariant data;
    QSqlRecord record;
    for (int i = 0; i < this->rowCount(); i++) {
        record = this->record(i);

        data = record.value("obverseimg");
        if (QString(data.typeName()) == "QByteArray") {
            query = QSqlQuery(database());
            query.prepare("INSERT INTO images (image) VALUES (?)");
            query.addBindValue(data);
            query.exec();
            data = query.lastInsertId();
            record.setValue("obverseimg", data);
        }

        data = record.value("reverseimg");
        if (QString(data.typeName()) == "QByteArray") {
            query = QSqlQuery(database());
            query.prepare("INSERT INTO images (image) VALUES (?)");
            query.addBindValue(data);
            query.exec();
            data = query.lastInsertId();
            record.setValue("reverseimg", data);
        }

        setRecord(i, record);
    }

    submitAll();
*/
    sql = "REPLACE INTO settings (title, value) VALUES ('Version', 'M2')";
    QSqlQuery(sql, database());

    return true;
}

QString CoinModel::makeFilter(const QModelIndex &index, const QString field) const
{
    if (isEmtyField(index, field))
        return "IFNULL(" + field + ",'')=''";
    else
        return field + "=?";
}

QVariant CoinModel::data(const QModelIndex &index, int role) const {
    QVariant value;
    if(role < Qt::UserRole)
    {
        value = QSqlTableModel::data(index, role);
    }
    else
    {
        if (role == m_countRoleIndex) {
            QModelIndex modelIndex;
            QVariant data;

            QSqlQuery query = QSqlQuery(database());
            QString sql = "SELECT COUNT(*) FROM coins WHERE status='owned'";
            sql += " AND " + makeFilter(index, "subjectshort");
            sql += " AND " + makeFilter(index, "series");
            sql += " AND " + makeFilter(index, "value");
            sql += " AND " + makeFilter(index, "country");
            sql += " AND " + makeFilter(index, "unit");
            sql += " AND " + makeFilter(index, "year");
            sql += " AND " + makeFilter(index, "mintmark");
            sql += " AND " + makeFilter(index, "quality");
            sql += " AND " + makeFilter(index, "catalognum1");

            query.prepare(sql);

            if (!isEmtyField(index, "subjectshort")) {
                modelIndex = this->index(index.row(), fieldIndex("subjectshort"));
                data = modelIndex.data();
                query.addBindValue(data);
            }
            if (!isEmtyField(index, "series")) {
                modelIndex = this->index(index.row(), fieldIndex("series"));
                data = modelIndex.data();
                query.addBindValue(data);
            }
            if (!isEmtyField(index, "value")) {
                modelIndex = this->index(index.row(), fieldIndex("value"));
                data = modelIndex.data();
                query.addBindValue(data);
            }
            if (!isEmtyField(index, "country")) {
                modelIndex = this->index(index.row(), fieldIndex("country"));
                data = modelIndex.data();
                query.addBindValue(data);
            }
            if (!isEmtyField(index, "unit")) {
                modelIndex = this->index(index.row(), fieldIndex("unit"));
                data = modelIndex.data();
                query.addBindValue(data);
            }
            if (!isEmtyField(index, "year")) {
                modelIndex = this->index(index.row(), fieldIndex("year"));
                data = modelIndex.data();
                query.addBindValue(data);
            }
            if (!isEmtyField(index, "mintmark")) {
                modelIndex = this->index(index.row(), fieldIndex("mintmark"));
                data = modelIndex.data();
                query.addBindValue(data);
            }
            if (!isEmtyField(index, "quality")) {
                modelIndex = this->index(index.row(), fieldIndex("quality"));
                data = modelIndex.data();
                query.addBindValue(data);
            }
            if (!isEmtyField(index, "catalognum1")) {
                modelIndex = this->index(index.row(), fieldIndex("catalognum1"));
                data = modelIndex.data();
                query.addBindValue(data);
            }

            query.exec();
            query.first();
            value = query.record().value(0);
        }
        else if (role == m_imgheightRoleIndex) {
            QModelIndex modelIndex = this->index(index.row(), fieldIndex("image"));
            value = modelIndex.data();
            QImage image = QImage();
            image.loadFromData(value.toByteArray());
            value = image.height();
        }
        else if (role == m_imgwidthRoleIndex) {
            QModelIndex modelIndex = this->index(index.row(), fieldIndex("image"));
            value = modelIndex.data();
            QImage image = QImage();
            image.loadFromData(value.toByteArray());
            value = image.width();
        }
        else if (role == m_obverseimgRoleIndex) {
            QModelIndex modelIndex = this->index(index.row(), fieldIndex("obverseimg"));
            QVariant data = modelIndex.data();

            if (data.type() == QVariant::ByteArray) {
                QByteArray ba = data.toByteArray();
                value = ba.toBase64();
            }
            else {
                QSqlQuery query = QSqlQuery(database());
                QString sql = "SELECT image FROM images WHERE id=?";

                query.prepare(sql);
                query.addBindValue(data);

                query.exec();
                if (query.first()) {
                    value = query.record().value(0);
                    if (value.type() == QVariant::ByteArray) {
                        QByteArray ba = value.toByteArray();
                        value = ba.toBase64();
                    }
                }
            }
        }
        else if (role == m_reverseimgRoleIndex) {
            QModelIndex modelIndex = this->index(index.row(), fieldIndex("reverseimg"));
            QVariant data = modelIndex.data();

            if (data.type() == QVariant::ByteArray) {
                QByteArray ba = data.toByteArray();
                value = ba.toBase64();
            }
            else {
                QSqlQuery query = QSqlQuery(database());
                QString sql = "SELECT image FROM images WHERE id=?";

                query.prepare(sql);
                query.addBindValue(data);

                query.exec();
                if (query.first()) {
                    value = query.record().value(0);
                    if (value.type() == QVariant::ByteArray) {
                        QByteArray ba = value.toByteArray();
                        value = ba.toBase64();
                    }
                }
            }
        }
        else {
            int columnIdx = role - Qt::UserRole - 1;
            QModelIndex modelIndex = this->index(index.row(), fieldIndex(COLUMN_NAMES[columnIdx]));
            value = modelIndex.data();
            if (value.type() == QVariant::ByteArray) {
                QByteArray ba = value.toByteArray();
                value = ba.toBase64();
            }
        }
    }
    return value;
}

void CoinModel::setData(int row, const QString & field_name, QVariant new_value) {
    // TODO: Update column index by field_name
    QModelIndex idx = index(row, 1);
    QSqlTableModel::setData(idx, new_value);
    submitAll();
}

QMap<QString, QVariant> CoinModel::get(int i) const {
    QMap<QString, QVariant> model;
    QHash<int, QByteArray> roles = roleNames();
    QModelIndex idx = index(i, 0);
    foreach (int role, roles.keys()) {
        if (role > Qt::UserRole)
            model[roles.value(role)] = data(idx, role);
    }
    return model;
}

void CoinModel::setSeries(int seriesId) {
    if (m_seriesId != seriesId) {
        m_seriesId = seriesId;
        if (seriesId == 0) {
            setFilter(DEFAULT_FILTER);
        }
        else {
            QString series = m_seriesModel[seriesId];
            QString escapedSeries = series.replace('\'', "''");
            if (m_view == CommemorativeWorld)
                setFilter("country = '" + escapedSeries + "' AND " + DEFAULT_FILTER);
            else
                setFilter("series = '" + escapedSeries + "' AND " + DEFAULT_FILTER);
        }
    }
}

void CoinModel::setView(CoinModel::View view)
{
    m_view = view;

    QSqlQuery query = QSqlQuery("REPLACE INTO settings (title, value) VALUES ('mobile_view', ?)", database());
    if (m_view == CommemorativeWorld)
        query.addBindValue(QVariant("CommemorativeWorld"));
    else
        query.addBindValue(QVariant("CommemorativeCountry"));
    query.exec();

    emit changed();
}

void CoinModel::clone(int row)
{
    QString sql = "SELECT image FROM images WHERE id=?";
    QVariant data, value;
    QVariant img;
    QSqlQuery query;

    QSqlRecord rec = record(row);
    rec.setNull("id");
    rec.setValue("status", "owned");

    QDateTime currentTime = QDateTime::currentDateTimeUtc();
    rec.setValue("updatedat", currentTime.toString(Qt::ISODate));
    rec.setValue("createdat", currentTime.toString(Qt::ISODate));

    data = rec.value("obverseimg");
    if (data.type() == QVariant::ByteArray) {
        img = data;
    }
    else {
        query = QSqlQuery(database());

        query.prepare(sql);
        query.addBindValue(data);
        query.exec();
        if (query.first())
            img = query.record().value(0);
    }

    if (img.isValid()) {
        query = QSqlQuery(database());
        query.prepare("INSERT INTO images (image) VALUES (?)");
        query.addBindValue(img);
        query.exec();

        value = query.lastInsertId();
        rec.setValue("obverseimg", value);
    }

    img.clear();

    data = rec.value("reverseimg");
    if (data.type() == QVariant::ByteArray) {
        img = data;
    }
    else {
        query = QSqlQuery(database());

        query.prepare(sql);
        query.addBindValue(data);
        query.exec();
        if (query.first())
            img = query.record().value(0);
    }

    if (img.isValid()) {
        query = QSqlQuery(database());
        query.prepare("INSERT INTO images (image) VALUES (?)");
        query.addBindValue(img);
        query.exec();

        value = query.lastInsertId();
        rec.setValue("reverseimg", value);
    }

    insertRecord(-1, rec);
    submitAll();
    select();
}

void CoinModel::remove(int row) {
    QModelIndex modelIndex;
    QVariant data;

    QSqlQuery query = QSqlQuery(database());
    QString sql = "SELECT * FROM coins WHERE status='owned'";
    sql += " AND " + makeFilter(index(row, 0), "subjectshort");
    sql += " AND " + makeFilter(index(row, 0), "series");
    sql += " AND " + makeFilter(index(row, 0), "value");
    sql += " AND " + makeFilter(index(row, 0), "country");
    sql += " AND " + makeFilter(index(row, 0), "unit");
    sql += " AND " + makeFilter(index(row, 0), "year");
    sql += " AND " + makeFilter(index(row, 0), "mintmark");
    sql += " AND " + makeFilter(index(row, 0), "quality");
    sql += " AND " + makeFilter(index(row, 0), "catalognum1");

    query.prepare(sql);

    if (!isEmtyField(index(row, 0), "subjectshort")) {
        modelIndex = index(row, fieldIndex("subjectshort"));
        data = modelIndex.data();
        query.addBindValue(data);
    }
    if (!isEmtyField(index(row, 0), "series")) {
        modelIndex = index(row, fieldIndex("series"));
        data = modelIndex.data();
        query.addBindValue(data);
    }
    if (!isEmtyField(index(row, 0), "value")) {
        modelIndex = index(row, fieldIndex("value"));
        data = modelIndex.data();
        query.addBindValue(data);
    }
    if (!isEmtyField(index(row, 0), "country")) {
        modelIndex = index(row, fieldIndex("country"));
        data = modelIndex.data();
        query.addBindValue(data);
    }
    if (!isEmtyField(index(row, 0), "unit")) {
        modelIndex = index(row, fieldIndex("unit"));
        data = modelIndex.data();
        query.addBindValue(data);
    }
    if (!isEmtyField(index(row, 0), "year")) {
        modelIndex = index(row, fieldIndex("year"));
        data = modelIndex.data();
        query.addBindValue(data);
    }
    if (!isEmtyField(index(row, 0), "mintmark")) {
        modelIndex = index(row, fieldIndex("mintmark"));
        data = modelIndex.data();
        query.addBindValue(data);
    }
    if (!isEmtyField(index(row, 0), "quality")) {
        modelIndex = index(row, fieldIndex("quality"));
        data = modelIndex.data();
        query.addBindValue(data);
    }
    if (!isEmtyField(index(row, 0), "catalognum1")) {
        modelIndex = index(row, fieldIndex("catalognum1"));
        data = modelIndex.data();
        query.addBindValue(data);
    }

    query.exec();
    if (query.first()) {
        QVariant img_ids[2];
        QSqlQuery queryDelImg;
        img_ids[0] = query.record().value("obverseimg");
        img_ids[1] = query.record().value("reverseimg");
        for (int i = 0; i < 2; i++) {
            if (img_ids[i].type() != QVariant::ByteArray) {
                queryDelImg = QSqlQuery("DELETE FROM images WHERE id=?", database());
                queryDelImg.addBindValue(img_ids[i]);
                queryDelImg.exec();
            }
        }

        QVariant id = query.record().value("id");
        QSqlQuery queryDel = QSqlQuery("DELETE FROM coins WHERE id=?", database());
        queryDel.addBindValue(id);
        queryDel.exec();
        select();
    }
}

QString CoinModel::folder()
{
    QDir dir(database().databaseName());
    if (!database().databaseName().isEmpty()) {
        dir.cdUp();
    }
    else {
        dir.setPath(QDesktopServices::storageLocation(QDesktopServices::DocumentsLocation));
    }
    return dir.absolutePath();
}

QString CoinModel::lastCollection()
{
    return m_settings.value("lastCollection").toString();
}

QString CoinModel::fileTitle()
{
    QFileInfo file(database().databaseName());
    return file.baseName();
}
